<?php
class Admincp_Model_Category extends Zend_Db_Table_Abstract
{
    protected $_db;

    public function __construct()
    {
        $this->_db = Zend_Registry::get('db');
    }

    public function listCategory($locale)
    {
        $sql = $this->_db->select()
            ->from('category AS c')
            ->join('category_language AS cl', 'c.id = cl.category_id', array('name'))
            ->join('language AS l', 'cl.language_id = l.id', null)
            ->where('l.locale = ?', $locale)
            ->order('c.id DESC');
        return $sql;
    }

    public function listCategorySelectbox()
    {
        $sql = $this->_db->select()
            ->from('category AS c', array('id'))
            ->join('category_language AS cl', 'c.id = cl.category_id', array('name'))
            ->join('language AS l', 'cl.language_id = l.id', null)
            ->where('l.locale = ?', 'vi')
            ->order('c.id DESC');
        $result = $this->_db->fetchAll($sql);
        return $result;
    }

    public function getCategoryById($id)
    {
        $sql = $this->_db->select()
            ->from('category_language AS cl', array('name'))
            ->join('language AS l', 'cl.language_id = l.id', array('locale'))
            ->where('cl.category_id = ?', $id);
        $result = $this->_db->fetchAll($sql);
        return $result;
    }

    public function getCategoryNameById($id)
    {
        $sql = $this->_db->select()
            ->from('category_language AS cl', array('name'))
            ->join('language AS l', 'cl.language_id = l.id', null)
            ->where('cl.category_id = ?', $id)
            ->where('l.locale = ?', 'vi');
        $result = $this->_db->fetchOne($sql);
        return $result;
    }

    public function checkCategory($name, $id = null)
    {
        $sql = $this->_db->select()
            ->from('category_language')
            ->where('name = ?', $name);
        if($id != null) {
            $sql = $sql->where('category_id <> ?', $id);
        }
        $result = $this->_db->fetchAll($sql);
        return count($result);
    }

    public function addCategory($data)
    {
        $this->_db->insert('category',$data);
        return $this->_db->lastInsertId();
    }

    public function addCategoryLanguage($data)
    {
        $this->_db->insert('category_language',$data);
    }

    public function deleteCategory($id)
    {
        $where = $this->getAdapter()->quoteInto('id = ?', $id);
        $this->_db->delete('category', $where);
        $where = $this->getAdapter()->quoteInto('category_id = ?', $id);
        $this->_db->delete('category_language', $where);
    }

    public function deleteMultiCategory($listId)
    {
        foreach($listId as $id) {
            $this->deleteCategory($id);
        }
    }

    public function updateCategory($data, $id)
    {
        $where = $this->getAdapter()->quoteInto('id = ?', $id);
        $this->_db->update('category', $data, $where);
    }

    public function updateCategoryLanguage($data,$id)
    {
        $where = 'category_id = '.$id.' AND language_id = '.$data['language_id'];
        $this->_db->update('category_language', $data, $where);
    }

    public function searchCategory($data, $locale)
    {
        $sql = $this->_db->select()
            ->from('category AS c')
            ->join('category_language AS cl', 'c.id = cl.category_id', array('name'))
            ->join('language AS l', 'cl.language_id = l.id', null)
            ->where('l.locale = ?', $locale);
        if($data['name'] != '') {
            $sql = $sql->where('cl.name_plaintext LIKE ?', $data['name']);
        }
        if($data['status'] != 0) {
            $sql = $sql->where('c.status = ?', $data['status']);
        }
        return $sql;
    }
}
